package bainuo

import groovy.sql.Sql

def execute() {
    def conn = null
    try {
        // 创建数据库连接
        conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
        List<Map<String, String>> results = new ArrayList<>()

        // 新的查询语句
        def sql = """
            select * from 
(select pk_praybill_b 请购单表体id,po1.vbillcode 请购单号,po1.dbilldate 请购单日期,bdm.code 物料编码,bdm.name 物料名称 ,pob1.nastnum 请购数量 
from po_praybill  po1 --请购单主表
inner join po_praybill_b pob1 on  po1.pk_praybill=pob1.pk_praybill and po1.dr=0 and pob1.dr=0 --请购单子表
inner join  bd_material bdm on pob1. pk_srcmaterial=bdm.pk_material and bdm.dr=0 --物料信息
where po1.dr=0
) z1 --请购单信息
left join 
(select cpraybillbid 订单请购单表体id,pb.pk_order_b 订单明细id,p.dbilldate 订单日期,p.vbillcode 订单号,p.taudittime 订单审核日期, a1.code 物料编码1,a1.name 物料名称1,a2.name 计量单位, pb.nnum 订单数量, pb.nqtorigprice 订单无税单价,pb.nqtorigtaxprice 订单含税单价,pb.norigmny 订单无税金额,pb.norigtaxmny 订单含税金额,pb.ntax 订单税额,pb.ntaxrate 订单税率,pb.dplanarrvdate 计划到货日期 
from po_order  p  --采购订单主表
inner join po_order_b pb on p.pk_order=pb.pk_order and p.dr=0 and pb.dr=0 --采购订单子表
inner join bd_material a1 on pb.pk_material=a1.pk_material and a1.dr=0 
inner join bd_measdoc a2 on pb.cunitid=a2.pk_measdoc and a2.dr=0 --物料
where p.dr=0
) z2  --采购订单信息
on z1.请购单表体id=z2.订单请购单表体id  --采购订单关联请购单
left join 
(select pob.pk_order_b 到货单订单明细id,pob.pk_arriveorder_b 到货单明细id,po.vbillcode 到货单号,po.dbilldate 到货日期,pob.nnum 到货数量,pob.ntaxmny 到货金额 
from po_arriveorder po  --到货单主表
inner join  po_arriveorder_b  pob on po.pk_arriveorder=pob.pk_arriveorder and po.dr=0 and pob.dr=0 --到货单子表
where po.dr=0
) z3 --到货单信息
on z2.订单明细id=z3.到货单订单明细id  --到货单关联订单
left join
(select icb.csourcetype 入库单来源单据类型id,icb.csourcebillbid 入库单来源单据id,icb.cgeneralbid 入库单明细id,ich.vbillcode 入库单号,ich.dbilldate 入库单日期,icb.nnum 入库数量,icb.ntaxmny 入库金额 from ic_purchasein_h ich --采购入库单主表
inner join ic_purchasein_b icb on ich.cgeneralhid=icb.cgeneralhid and ich.dr=0 and icb.dr=0 --采购入库单子表
where icb.csourcetype='23' and icb.dr=0
) z4 on z3.到货单明细id=z4.入库单来源单据id  --采购入库单信息
left join 
(select  pob2.pk_order_b 发票订单id,pob2.csourcebid 发票入库单明细id,po2.vbillcode 发票号,vordercode,po2.dbilldate 发票日期,pob2.nnum 发票数量,pob2.ntaxmny 发票金额 
from  po_invoice po2 --采购发票主表
inner join po_invoice_b  pob2 on po2.pk_invoice=pob2.pk_invoice and po2.dr=0 and pob2.dr=0 --采购发票子表
where csourcetypecode='45' and po2.dr=0
) z5 --采购发票信息
on z2.订单明细id=z5.发票订单id and z5.发票入库单明细id=z4.入库单明细id  --采购发票关联订单和入库单
left join 
(
(select pk_order_b,nmny ,pk_apply_b,申请付款日期 from 
(select po_order_payplan.pk_order_payplan,pk_order_b,po_order_payplan_b.vordercode, corderrowno,pk_order_payplan_b,po_order_payplan_b.nmny 
from po_order_payplan_b  --付款计划子表
inner join po_order_payplan on po_order_payplan.pk_order_payplan=po_order_payplan_b.pk_order_payplan and po_order_payplan.dr=0 and po_order_payplan_b.dr=0 --付款计划主表
) a --付款计划信息
inner join
(select apb.sourcesystypecode,fsrcbilltypecode, pk_fsrcbillrowid 付款申请订单行id,ap.vbillno 申请单号, ap.applydate 申请付款日期,ap.dapprovedate 审批日期,apb.applymny 申请付款金额 ,orderno 订单号
,ap.srcbillno 来源单据号,ap.pk_srcbilltypeid 来源单据类型 , apb.srcbillno 明细来源单据号 ,apb.pk_srcbill 明细来源单据主键, srcbilltypecode 明细来源单据类型编码
,pk_fsrcbillrowid,pk_apply_b
from cmp_apply ap  --付款申请单主表
inner join cmp_apply_b apb on  ap.pk_apply=apb.pk_apply and ap.dr=0 and apb.dr=0 -- 付款申请单子表
where fsrcbilltypecode='21' and ap.dr=0
) b --付款申请单信息
on a.pk_order_payplan=b.pk_fsrcbillrowid  --付款申请单关联付款计划信息
union
(select  pk_fsrcbillrowid as pk_order_b,apb.applymny as nmny,pk_apply_b, ap.applydate 申请付款日期 
from cmp_apply ap  --付款申请单主表
inner join cmp_apply_b apb on  ap.pk_apply=apb.pk_apply where fsrcbilltypecode='21' and srcbilltypecode='F1' and ap.dr=0 and apb.dr=0)  --付款申请单子表
) --所有付款申请单信息
) z6  on z2.订单明细id=z6.pk_order_b  --付款申请单关联订单

left join  
(  select top_itemid, a.billdate,money from ap_paybill a inner join   ap_payitem   b  on a.pk_paybill=b.pk_paybill and a.dr=0 and b.dr=0 where top_billtype='36D1' and a.dr=0)z7 on z6.pk_apply_b=z7.top_itemid
        """

        def rows = conn.rows(sql)

        for (def row : rows) {
            if (row != null) {
                Map<String, String> item = new HashMap<>();
                // 填充item映射，使用新查询中的字段名
                item.put("pk_praybill_b", row["请购单表体id"]?.toString())
                item.put("vbillcode_praybill", row["请购单号"]?.toString())
                item.put("dbilldate_praybill", row["请购单日期"]?.toString())
                item.put("material_code", row["物料编码"]?.toString())
                println(row["物料编码"]?.toString())

                item.put("material_name", row["物料名称"]?.toString())

                println(row["物料名称"]?.toString())
                item.put("nastnum", row["请购数量"]?.toString())
                item.put("cpraybillbid", row["订单请购单表体id"]?.toString())
                item.put("pk_order_b_order", row["订单明细id"]?.toString())
                item.put("dbilldate_order", row["订单日期"]?.toString())
                item.put("vbillcode_order", row["订单号"]?.toString())
                item.put("taudittime", row["订单审核日期"]?.toString())
                item.put("material_code_order", row["物料编码1"]?.toString())
                item.put("material_name_order", row["物料名称1"]?.toString())
                item.put("unit_name", row["计量单位"]?.toString())
                item.put("nnum_order", row["订单数量"]?.toString())
                item.put("nqtorigprice", row["订单无税单价"]?.toString())
                item.put("nqtorigtaxprice", row["订单含税单价"]?.toString())
                item.put("norigmny", row["订单无税金额"]?.toString())
                item.put("norigtaxmny", row["订单含税金额"]?.toString())
                item.put("ntax", row["订单税额"]?.toString())
                item.put("ntaxrate", row["订单税率"]?.toString())
                item.put("dplanarrvdate", row["计划到货日期"]?.toString())
                item.put("pk_order_b_arrive", row["到货单订单明细id"]?.toString())
                item.put("pk_arriveorder_b", row["到货单明细id"]?.toString())
                item.put("vbillcode_arrive", row["到货单号"]?.toString())
                item.put("dbilldate_arrive", row["到货日期"]?.toString())
                item.put("nnum_arrive", row["到货数量"]?.toString())
                item.put("ntaxmny_arrive", row["到货金额"]?.toString())
                item.put("csourcetype", row["入库单来源单据类型id"]?.toString())
                item.put("csourcebillbid", row["入库单来源单据id"]?.toString())
                item.put("cgeneralbid", row["入库单明细id"]?.toString())
                item.put("vbillcode_in", row["入库单号"]?.toString())
                item.put("dbilldate_in", row["入库单日期"]?.toString())
                item.put("nnum_in", row["入库数量"]?.toString())
                item.put("ntaxmny_in", row["入库金额"]?.toString())
                item.put("pk_order_b_invoice", row["发票订单id"]?.toString())
                item.put("csourcebid_invoice", row["发票入库单明细id"]?.toString())
                item.put("vbillcode_invoice", row["发票号"]?.toString())
                item.put("vordercode", row["vordercode"]?.toString())
                item.put("发票日期", row["发票日期"]?.toString())
                item.put("nnum_invoice", row["发票数量"]?.toString())
                item.put("ntaxmny_invoice", row["发票金额"]?.toString())
                item.put("pk_order_b_payment", row["pk_order_b"]?.toString())
                item.put("nmny_payment", row["nmny"]?.toString())
                item.put("payment_date", row["申请付款日期"]?.toString())

                results.add(item);
            }
        }

        push(["items": results])
    } catch (Exception e) {
        e.printStackTrace()
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}

def push(data) {
    println "-------------------------请购单任务启动-------------------------"
    try {
        def items = data.get("items")

        // 在这里执行清空表的操作
        def conn = Sql.newInstance("jdbc:mysql://10.1.1.209:3306/sqlserver", "oper_dc", "operzADF2020qwer", "com.mysql.cj.jdbc.Driver")
        def truncateSql = "TRUNCATE TABLE procurement_info"
        conn.execute(truncateSql)
        conn.close()

        for (def item : items) {
            insertLog(item)
        }
        println "-------------------------传输完成-------------------------"
    } catch (Exception e) {
        e.printStackTrace();
        return ["state": 0, "Ascript": "请求参数错误,请检查"]
    }
}
static def insertLog(Map<String, String> params) {
    def conn = null
    try {
        // 连接数据库...
        conn = Sql.newInstance("jdbc:mysql://10.1.1.209:3306/sqlserver", "oper_dc", "operzADF2020qwer", "com.mysql.cj.jdbc.Driver")

        // 准备插入
        def insertKeys = []
        def insertValues = []
        def placeholders = []

        for (def entry : params.entrySet()) {
            insertKeys.add(entry.key)
            insertValues.add(entry.value)
            placeholders.add('?')
        }

        // 构建插入语句
        def sqlStatement = "INSERT INTO procurement_info(" + insertKeys.join(", ") + ") VALUES (" + placeholders.join(", ") + ")"

        // 执行插入操作
        conn.execute(sqlStatement, insertValues.toArray())

        println "Record inserted successfully with pk_order_b_order :[${params['pk_order_b_order']}]"
    } catch (Exception e) {
        e.printStackTrace()
        println "Error inserting record: ${e.message}"
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}